<?

/**
* fetch the header info, TR_DATE
* 
* @param  string    $COracle
* @param  string    $jobId
* @param  string    $srcDb
* @param  string    $resHeader
*
*/
function fetchTxHeader(&$COracle,
                       &$jobId,
                       &$srcDb,
                       &$resHeader){

  $query  = "SELECT TR_DATE
             FROM ".$srcDb." 
             WHERE  JOB_ID='".$jobId."' ";
             
  $COracle->execFetchQuery($query,
                           $nrows,
                           $resHeader);
}

/**
* fetch the detail info
* 
* @param  string    $COracle
* @param  string    $jobId
* @param  string    $idTx
* @param  string    $srcDb
* @param  string    $resDetail
*
*/
function fetchTxDetail(&$COracle,
                       &$jobId,
                       &$idTx,
                       &$srcDb,
                       &$resDetail){

  $query  = "SELECT *
             FROM ".$srcDb." 
             WHERE  JOB_ID='".$jobId."' 
             ORDER BY NO ASC ";
             
  $COracle->execFetchQuery($query,
                           $nrows,
                           $resDetail);
}

/**
* fetch the category name
* 
* @param  string    $COracle
* @param  string    $iCategory
* @param  string    $srcDb
* @param  string    $resCategory
*
*/
function fetchCategory(&$COracle,
                       &$iCategory,
                       &$srcDb,
                       &$resCategory){

  $query  = "SELECT CAT_NAME
             FROM ".$srcDb." 
             WHERE  CAT_ID='".$iCategory."' ";
             
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
                           
  $resCategory = $result["CAT_NAME"][0];
}

/**
* fetch the group name
* 
* @param  string    $COracle
* @param  string    $iGroup
* @param  string    $srcDb
* @param  string    $resGroup
*
*/          
function fetchGroup( &$COracle,
                     &$iGroup,
                     &$srcDb,
                     &$resGroup){

  $query  = "SELECT GRP_NAME
             FROM ".$srcDb." 
             WHERE  GRP_ID='".$iGroup."' ";
             
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
                           
  $resGroup = $result["GRP_NAME"][0];
}

/**
* fetch the ID of category by category name
*
* @param  string    $COracle
* @param  string    $nmCategory
* @param  string    $srcDb
* @param  string    $idCategory
*
*/
function fetchIdCategory(&$COracle,
                         &$nmCategory,
                         &$srcDb,
                         &$idCategory){
                        
  $query = "SELECT CAT_ID 
            FROM ".$srcDb." 
            WHERE  CAT_NAME='".$nmCategory."' ";
            
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
  
  $idCategory = $result["CAT_ID"][0];
}

/**
* fetch the ID of group by group name
*
* @param  string    $COracle
* @param  string    $nmGroup
* @param  string    $srcDb
* @param  string    $idGroup
*
*/
function fetchIdGroup(&$COracle,
                      &$nmGroup,
                      &$srcDb,
                      &$idGroup){
                        
  $query = "SELECT GRP_ID 
            FROM ".$srcDb." 
            WHERE  GRP_NAME='".$nmGroup."' ";
            
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
  
  $idGroup = $result["GRP_ID"][0];
}

/**
* fetch the list of valid transactions
*
* @param  string    $COracle
* @param  string    $condt      the condition
* @param  string    $srcDb
* @param  string    $lstResult
*
*/
function fetchLstTransaction(&$COracle,
                             $condt,
      	                     &$srcDb,
      	                     &$nResult,
      	                     &$lstResult){
      	                      
  $query  = "SELECT JOB_ID,
                    REFERENCE,
                    TR_DATE,
                    STATUS
             FROM ".$srcDb."
             ".$condt." 
             ORDER BY TR_DATE DESC ";
             
  $COracle->execFetchQuery($query,
                           $nResult,
                           $lstResult);
}


/**
* fetch list of detail data
* 
* @param  string    $COracle
* @param  string    $jobId
* @param  string    $srcDb
* @param  integer   $nDetail
* @param  string    $lstDetail
*
*/
function fetchLstDataDetail(&$COracle,
                            &$jobId,
                            &$srcDb,
                            &$nDetail,
                            &$lstDetail){
                              
  $query  = "SELECT * 
             FROM ".$srcDb." 
             WHERE  JOB_ID='".$jobId."' ";
             
  $COracle->execFetchQuery($query,
                           $nDetail,
                           $lstDetail);
}

/**
* Fetch category name by id
*
* @param  string    $COracle
* @param  integer   $idCategory
* @param  string    $srcDb
* @param  string    $nmCategory
*
*/
function fetchCatById(&$COracle,
  		                &$idCategory,
  		                &$srcDb,
  		                &$nmCategory){

  $query  = "SELECT CAT_NAME 
             FROM ".$srcDb." 
             WHERE  CAT_ID=".$idCategory." ";
             
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
                           
  $nmCategory = $result["CAT_NAME"][0];
}

/**
* Fetch group name by id
*
* @param  string    $COracle
* @param  integer   $idGroup
* @param  string    $srcDb
* @param  string    $nmGroup
*
*/
function fetchGrpById(&$COracle,
  		                &$idGroup,
  		                &$srcDb,
  		                &$nmGroup){

  $query  = "SELECT GRP_NAME 
             FROM ".$srcDb." 
             WHERE  GRP_ID=".$idGroup." ";
             
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
                           
  $nmGroup = $result["GRP_NAME"][0];
}

/**
* Fetch the old received qty STATUS
*
* @param  string    $COracle
* @param  string    $odrRef     order reference
* @param  string    $odrNo      order tx number
* @param  string    $srcDb
* @param  string    $resOldDlvr the old qty status
*
*/
function fetchOldReceived(&$COracle,
                          &$odrRef,
                          &$odrNo,
                          &$srcDb,
                          &$resOldDlvr){

  $query  = "SELECT DLVR_QTY 
             FROM ".$srcDb." 
             WHERE  JOB_ID='".$odrRef."' 
                    AND NO=".$odrNo." ";

  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
                           
  $resOldDlvr = $result["DLVR_QTY"][0];
}

/**
* check if one of the 
* tx of this job_id has received qty
* greater than 0
* YES: If all has equaled than
*      isBtbIssued   = TRUE else
*      isOutstanding = TRUE 
* NO : isOutstanding = TRUE
*
*/
function chkDlvrQuantity( &$COracle,
                          &$odrRef,
                          &$srcDb,
                          &$isOutstanding,
                          &$isBtbIssued,
                          &$isActive){

  $query  = "SELECT QTY,
                    DLVR_QTY
             FROM ".$srcDb." 
             WHERE  JOB_ID='".$odrRef."' ";
             
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
                           
  for($iRow = 0; $iRow < $nrows; $iRow++){
    
    /**
    * Check if all of the tx has been received (equaled)
    * EQUAL: need to finish the loop with out
    *        being exception handled
    * = 0  : set isActive
    * > 0  : set isOutstanding and bail out!
    * 
    */
    if($result["QTY"][$iRow] == $result["DLVR_QTY"][$iRow]){
      
      if($isActive != true){
      
        $isBtbIssued = true;
      }
    }else
    if($result["DLVR_QTY"][$iRow] == 0){
      
      $isActive      = true;
      $isBtbIssued   = false;
      $isOutstanding = false;
    }else{
     
      $isBtbIssued    = false;
      $isActive       = false;
      $isOutstanding  = true;
      break;
    }
  }
}

/**
* Fetch list of convertion
*
* @param  string    $COracle
* @param  string    $srcLevelD
* @param  string    $srcUnit
* @param  string    $srcDb  
* @param  string    $lstConvertion   
* 
*/
function fetchLstConvertion(&$COracle,
                            &$srcLevelD,
                            &$srcUnit,
                            &$srcDb,
                            &$lstConvertion){
                              
  $query  = "SELECT UNIT_TO ".
            "FROM ".$srcDb." ".
            "WHERE  LEVELD >= ".$srcLevelD." 
                    AND UNIT_NAME='".$srcUnit."' 
                    AND STATUS='"._ACTIVE."' ";
  
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
                           
  $lstConvertion = $result["UNIT_TO"];
}

/**
* fetch the value of this convertion criteria
*
* @param  string    $COracle
* @param  string    $srcFrom
* @param  string    $srcTo
* @param  string    $srcDb
* @param  string    $qtyConvertion
*
*/
function fetchQtyConvertion(&$COracle,
                            &$srcFrom,
                            &$srcTo,
                            &$srcDb,
                            &$qtyConvertion){
                            
  $query  = "SELECT UNIT_QTY 
             FROM ".$srcDb." 
             WHERE  UNIT_NAME='".$srcFrom."' 
                    AND UNIT_TO='".$srcTo."' ";
                    
  $COracle->execFetchQuery($query,
                           $nrows,
                           $result);
  
  $qtyConvertion = $result["UNIT_QTY"][0];
}
?>